package com.salesmanager.core.business.customer.dao;

import com.mysema.query.jpa.JPQLQuery;
import com.mysema.query.jpa.impl.JPAQuery;
import com.salesmanager.core.business.customer.model.*;

import com.salesmanager.core.business.generic.dao.SalesManagerEntityDaoImpl;
import com.salesmanager.core.business.generic.exception.ServiceException;
import com.salesmanager.core.business.statistics.model.CustomerStatisticsCriteria;

import com.salesmanager.core.business.statistics.model.StatisticsEntityForCustomer;
import com.salesmanager.core.utils.DateUtil;
import com.salesmanager.web.model.paging.PaginationDataList;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import javax.persistence.NoResultException;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository("customerDao")
public class CustomerDAOImpl extends SalesManagerEntityDaoImpl<Long, Customer> implements CustomerDAO {

    public CustomerDAOImpl() {
        super();
    }

    @Override
    public boolean saveDeviceToke(String username, String deviceToke, String appKey) {
        boolean b = true;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("update CUSTOMER C,CUSTOMER_AUTHS CA set c.DEVICE_TOKEN=:deviceToken,c.APP_KEY=:appKey where C.CUSTOMER_ID = CA.CUSTOMER_ID AND  IDENTIFIER=:tel");
        Query query = super.getEntityManager().createNativeQuery(stringBuilder.toString());
        query.setParameter("tel", username);
        query.setParameter("deviceToken", deviceToke);
        query.setParameter("appKey", appKey);
        int a = query.executeUpdate();
        if (a == 0) b = false;
        return b;
    }

    @Override
    public Customer getByTelphone(String telphone) {
        QCustomer qCustomer = QCustomer.customer;
        JPQLQuery query = new JPAQuery(getEntityManager());

        query.from(qCustomer)
                .leftJoin(qCustomer.groups).fetch()
                //账号凭证表
                .innerJoin(qCustomer.auths).fetch()
                .where(qCustomer.telphone.eq(telphone));

        return query.uniqueResult(qCustomer);
    }

    @Override
    public Customer getCustomer(Long id) {
        Customer customer = null;
        StringBuilder q = new StringBuilder("select c from Customer c where c.id=:id");

        Query queryQ = super.getEntityManager().createQuery(q.toString());
        queryQ.setParameter("id", id);

        try {
            customer = (Customer) queryQ.getSingleResult();
        } catch (NoResultException e) {

        }
        return customer;
    }

    @Override
    public PaginationDataList<StatisticsEntityForCustomer> listWithCustomer(CustomerStatisticsCriteria criteria) throws ServiceException {
        if (criteria == null) {
            throw new ServiceException("criteria is must be not null!");
        }

        PaginationDataList<StatisticsEntityForCustomer> result = new PaginationDataList<StatisticsEntityForCustomer>();
        StringBuilder sql = new StringBuilder();
        StringBuilder wheresql = new StringBuilder();
        StringBuilder countsql = new StringBuilder();
        StringBuilder sortsql = new StringBuilder();

        if (criteria.getId() != null) {
            wheresql.append("AND C.CUSTOMER_ID = :id ");
        }
        if (!StringUtils.isBlank(criteria.getNick())) {
            wheresql.append("AND C.CUSTOMER_NICK = :nick ");
        }
        if (!StringUtils.isBlank(criteria.getChannel())) {
            wheresql.append("AND C.CHANNEL = :channel ");
        }
        if (!StringUtils.isBlank(criteria.getTelphone())) {
            wheresql.append("AND C.TELPHONE like :tel ");
        }
        if (criteria.getLevel() != null) {
            wheresql.append("AND CA.LEVEL = :level ");
        }
        if (criteria.getDevice() != null) {
            if (criteria.getDevice() == 1) {
                wheresql.append("AND LENGTH(C.DEVICE_TOKEN) = 44 ");
            }
            if (criteria.getDevice() == 2) {
                wheresql.append("AND LENGTH(C.DEVICE_TOKEN) = 64 ");
            }
        }
        if (criteria.getStartTime() != null && !"".equals(criteria.getStartTime())) {
            wheresql.append("AND C.DATE_CREATED >= :startTime ");
        }
        if (criteria.getEndTime() != null && !"".equals(criteria.getEndTime())) {
            wheresql.append("AND C.DATE_CREATED <= :endTime ");
        }
        if (criteria.getSort() != null) {
            if (criteria.getSort() == 0) {
                sortsql.append("ORDER BY C.DATE_CREATED DESC");
            }
            if (criteria.getSort() == 1) {
                sortsql.append("ORDER BY CAD.NUM DESC");
            }
            if (criteria.getSort() == 2) {
                sortsql.append("ORDER BY CAD.NUM ASC");
            }
        }
        sql.append("SELECT C.CUSTOMER_ID, C.TELPHONE, C.CUSTOMER_NICK, CA.LEVEL, CA.INTEGRAL, CA.BALANCE, C.DATE_CREATED,C.DEVICE_TOKEN,CAD.NUM,C.CHANNEL ");
        sql.append("FROM CUSTOMER C INNER JOIN CUSTOMER_ACCOUNT CA ON C.CUSTOMER_ID = CA.CUSTOMER_ID " +
                "LEFT JOIN (SELECT *,COUNT(customer_task_details.STATUS) NUM FROM customer_task_details where customer_task_details.STATUS=2 " +
                "group by customer_task_details.CUSTOMER_ID) CAD on  C.CUSTOMER_ID = CAD.CUSTOMER_ID  where 1=1 ");
        sql.append(wheresql).append(sortsql);

        countsql.append("SELECT COUNT(*) ");
        countsql.append("FROM CUSTOMER C INNER JOIN CUSTOMER_ACCOUNT CA ON C.CUSTOMER_ID = CA.CUSTOMER_ID WHERE 1=1 ");
        countsql.append(wheresql);

        Query countQuery = super.getEntityManager().createNativeQuery(countsql.toString());
        Query query = super.getEntityManager().createNativeQuery(sql.toString());

        //分页
        if (criteria.getMaxCount() > 0) {
            query.setMaxResults(criteria.getMaxCount());
            query.setFirstResult(criteria.getStartIndex());
        }
        if (criteria.getStartTime() != null && !"".equals(criteria.getStartTime())) {
            query.setParameter("startTime", criteria.getStartTime());
            countQuery.setParameter("startTime", criteria.getStartTime());
        }

        if (criteria.getEndTime() != null && !"".equals(criteria.getEndTime())) {
            query.setParameter("endTime", criteria.getEndTime());
            countQuery.setParameter("endTime", criteria.getEndTime());
        }
        if (criteria.getId() != null) {
            query.setParameter("id", criteria.getId());
            countQuery.setParameter("id", criteria.getId());
        }
        if (criteria.getChannel() != null) {
            query.setParameter("channel", criteria.getChannel());
            countQuery.setParameter("channel", criteria.getChannel());
        }
        if (!StringUtils.isBlank(criteria.getNick())) {
            query.setParameter("nick", criteria.getNick());
            countQuery.setParameter("nick", criteria.getNick());
        }
        if (!StringUtils.isBlank(criteria.getTelphone())) {
            query.setParameter("tel", "%" + criteria.getTelphone() + "%");
            countQuery.setParameter("tel", "%" + criteria.getTelphone() + "%");
        }
        if (criteria.getLevel() != null) {
            query.setParameter("level", criteria.getLevel());
            countQuery.setParameter("level", criteria.getLevel());
        }

        //总数
        Number totalCount = (Number) countQuery.getSingleResult();
        result.setTotalCount(totalCount.intValue());

        String device = null;
        List rs = query.getResultList();
        List<StatisticsEntityForCustomer> resultList = new ArrayList<StatisticsEntityForCustomer>();
        for (int i = 0; i < rs.size(); i++) {
            Object[] o = (Object[]) rs.get(i);
            StatisticsEntityForCustomer sfc = new StatisticsEntityForCustomer();
            sfc.setId(Long.parseLong(o[0].toString()));
            sfc.setTelphone((String) o[1]);
            sfc.setNick((String) o[2]);
            sfc.setLevel((Integer) o[3]);
            sfc.setIntegral((Integer) o[4]);
            sfc.setBalance((BigDecimal) o[5]);
            sfc.setDateCreated(DateUtil.formatLongDateMinute((Date) o[6]));
            if (o[7] != null) {
                device = (String) o[7];
                if (device.length() == 44) {
                    sfc.setDeviceToken("Android");
                } else if (device.length() == 64) {
                    sfc.setDeviceToken("IOS");
                } else {
                    sfc.setDeviceToken("未知");
                }
            } else {
                sfc.setDeviceToken("未知");
            }
            if (o[8] != null) {
                sfc.setGetNumber(Integer.parseInt(o[8].toString()));

            } else {
                sfc.setGetNumber(0);
            }
            sfc.setChannel((String) o[9]);
            resultList.add(sfc);
        }
        result.setData(resultList);
        return result;
    }

    @Override
    public int getCustomerCount(String startTime, String endTime) throws ParseException {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from Customer c where 1=1 ");
        if (!StringUtils.isBlank(startTime)) {
            sql.append("and c.auditSection.dateCreated >= :startTime ");
        }
        if (!StringUtils.isBlank(endTime)) {
            sql.append("and c.auditSection.dateCreated <= :endTime ");
        }
        Query query = super.getEntityManager().createQuery(sql.toString());

        if (!StringUtils.isBlank(startTime)) {
            query.setParameter("startTime", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(startTime));
        }
        if (!StringUtils.isBlank(endTime)) {
            query.setParameter("endTime", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(endTime));
        }
        Number number = (Number) query.getSingleResult();
        return number.intValue();
    }

    @Override
    public CustomerAuths getCustomerAuthByAuthType(CustomerCriteria customerCriteria) {

        if (customerCriteria.getAuthType() == null) return null;

        CustomerAuths customerAuths = null;
        StringBuilder hql = new StringBuilder("SELECT ca from  CustomerAuths ca where ");

        if (customerCriteria.getAuthType() == 1) {
            hql.append("ca.identityType = 1 and ca.identifier = :mobilePhone");
        } else if (customerCriteria.getAuthType() == 2) {
            hql.append("ca.identityType = 2 and ca.identifier = :deviceId");
        }

        Query query = super.getEntityManager().createQuery(hql.toString());
        if (customerCriteria.getAuthType() == 1) {
            query.setParameter("mobilePhone", customerCriteria.getMobilePhone());
        } else if (customerCriteria.getAuthType() == 2) {
            query.setParameter("deviceId", customerCriteria.getDeviceId());
        }


        try {
            customerAuths = (CustomerAuths) query.getSingleResult();
        } catch (NoResultException e) {

        }

        return customerAuths;
    }
}
